from datetime import datetime
from decimal import Decimal
from django.db.models import Count, Sum
from django.db.models.functions import TruncDate
from deal.models import Project, WinningResultAnnouncement, Company, BidOpeningRecord, BidOpeningDetail
from .models import DailyProjectStats, IndustryConcentration, CompanyWinStats, AmountBucketStats


def _get_region(extra):
    if not isinstance(extra, dict):
        return ""
    return extra.get('province') or extra.get('region') or ""


def _get_city(extra):
    if not isinstance(extra, dict):
        return ""
    return extra.get('city') or ""


def _get_industry(project_type: str, extra):
    if isinstance(extra, dict) and extra.get('industry'):
        return extra.get('industry')
    return project_type or ""


def projects_count(
    begin: datetime | None,
    end: datetime | None,
    project_type: str | None = None,
    region: str | None = None,
    city: str | None = None,
    industry: str | None = None,
    group_by: str | None = None,
):
    qs = Project.objects.all()
    if begin is not None:
        qs = qs.filter(created_at__gte=begin)
    if end is not None:
        qs = qs.filter(created_at__lte=end)
    if project_type:
        qs = qs.filter(project_type=project_type)
    rows = list(qs.values('id', 'created_at', 'project_type', 'extra_data'))
    data = {}
    for r in rows:
        rg = _get_region(r['extra_data'])
        ct = _get_city(r['extra_data'])
        ind = _get_industry(r['project_type'], r['extra_data'])
        if region and rg != region:
            continue
        if city and ct != city:
            continue
        if industry and ind != industry:
            continue
        day = r['created_at'].date()
        if group_by == 'region':
            key = (day, rg)
        elif group_by == 'city':
            key = (day, ct)
        elif group_by == 'type':
            key = (day, r['project_type'] or "")
        elif group_by == 'industry':
            key = (day, ind)
        else:
            key = (day,)
        data[key] = data.get(key, 0) + 1
    result = []
    for k, v in sorted(data.items(), key=lambda x: x[0]):
        item = {'day': k[0], 'project_count': v}
        if group_by in ('region', 'city', 'type', 'industry') and len(k) > 1:
            name_map = {'region': 'region', 'city': 'city', 'type': 'project_type', 'industry': 'industry'}
            item[name_map[group_by]] = k[1]
        result.append(item)
    return result


def projects_count_precomputed(begin: datetime | None, end: datetime | None, project_type: str | None, region: str | None, city: str | None, industry: str | None, group_by: str | None):
    qs = DailyProjectStats.objects.all()
    if begin is not None:
        qs = qs.filter(day__gte=begin.date())
    if end is not None:
        qs = qs.filter(day__lte=end.date())
    if project_type:
        qs = qs.filter(project_type=project_type)
    if region:
        qs = qs.filter(region=region)
    if city:
        qs = qs.filter(city=city)
    if industry:
        qs = qs.filter(industry=industry)
    values = ['day']
    name_map = {'region': 'region', 'city': 'city', 'type': 'project_type', 'industry': 'industry'}
    if group_by in name_map:
        values.append(name_map[group_by])
    agg = qs.values(*values).annotate(project_count=Sum('project_count')).order_by('day')
    return list(agg)


def amount_buckets(
    begin: datetime | None,
    end: datetime | None,
    source: str = 'budget',
    region: str | None = None,
    city: str | None = None,
    industry: str | None = None,
):
    buckets = [
        (Decimal('0'), Decimal('500000')),
        (Decimal('500000'), Decimal('2000000')),
        (Decimal('2000000'), Decimal('10000000')),
        (Decimal('10000000'), None),
    ]
    labels = ['0-50万', '50-200万', '200-1000万', '1000万以上']
    data = []
    if source == 'budget':
        qs = Project.objects.all()
        if begin is not None:
            qs = qs.filter(created_at__gte=begin)
        if end is not None:
            qs = qs.filter(created_at__lte=end)
        rows = list(qs.values('budget_amount', 'project_type', 'extra_data'))
        amounts = []
        for r in rows:
            rg = _get_region(r['extra_data'])
            ct = _get_city(r['extra_data'])
            ind = _get_industry(r['project_type'], r['extra_data'])
            if region and rg != region:
                continue
            if city and ct != city:
                continue
            if industry and ind != industry:
                continue
            amounts.append(r['budget_amount'])
    else:
        qs = WinningResultAnnouncement.objects.select_related('project')
        if begin is not None:
            qs = qs.filter(announcement_date__gte=begin)
        if end is not None:
            qs = qs.filter(announcement_date__lte=end)
        rows = list(qs.values('winning_amount', 'project__project_type', 'project__extra_data'))
        amounts = []
        for r in rows:
            rg = _get_region(r['project__extra_data'])
            ct = _get_city(r['project__extra_data'])
            ind = _get_industry(r['project__project_type'], r['project__extra_data'])
            if region and rg != region:
                continue
            if city and ct != city:
                continue
            if industry and ind != industry:
                continue
            amounts.append(r['winning_amount'])
    for idx, (low, high) in enumerate(buckets):
        c = 0
        s = Decimal('0')
        for a in amounts:
            if a is None:
                continue
            if high is None:
                if a >= low:
                    c += 1
                    s += a
            else:
                if a >= low and a < high:
                    c += 1
                    s += a
        data.append({'bucket': labels[idx], 'count': c, 'sum': float(s)})
    return data


def amount_buckets_precomputed(begin: datetime | None, end: datetime | None, source: str, region: str | None, city: str | None, industry: str | None):
    qs = AmountBucketStats.objects.filter(source=source)
    if begin is not None:
        qs = qs.filter(period__gte=begin.strftime('%Y-%m'))
    if end is not None:
        qs = qs.filter(period__lte=end.strftime('%Y-%m'))
    if region:
        qs = qs.filter(region=region)
    if city:
        qs = qs.filter(city=city)
    if industry:
        qs = qs.filter(industry=industry)
    rows = qs.values('bucket').annotate(count=Sum('count'), total=Sum('total')).order_by('bucket')
    return [{'bucket': r['bucket'], 'count': r['count'], 'sum': float(r['total'] or 0)} for r in rows]


def company_win_rate(begin: datetime | None, end: datetime | None, region: str | None = None, city: str | None = None, industry: str | None = None):
    eligible_projects = Project.objects.all().values('id', 'project_type', 'extra_data')
    project_ids = set()
    for p in eligible_projects:
        rg = _get_region(p['extra_data'])
        ct = _get_city(p['extra_data'])
        ind = _get_industry(p['project_type'], p['extra_data'])
        if region and rg != region:
            continue
        if city and ct != city:
            continue
        if industry and ind != industry:
            continue
        project_ids.add(p['id'])
    br = BidOpeningRecord.objects.all()
    if begin is not None:
        br = br.filter(opening_time__gte=begin)
    if end is not None:
        br = br.filter(opening_time__lte=end)
    if project_ids:
        br = br.filter(project_id__in=list(project_ids))
    bod = BidOpeningDetail.objects.filter(bid_opening_record__in=br)
    bid_counts = bod.values('company_id').annotate(bid_count=Count('bid_opening_record_id', distinct=True))
    wra = WinningResultAnnouncement.objects.all()
    if begin is not None:
        wra = wra.filter(announcement_date__gte=begin)
    if end is not None:
        wra = wra.filter(announcement_date__lte=end)
    if project_ids:
        wra = wra.filter(project_id__in=list(project_ids))
    win_counts = wra.values('winning_company_id').annotate(win_count=Count('id'))
    bid_map = {x['company_id']: x['bid_count'] for x in bid_counts}
    win_map = {x['winning_company_id']: x['win_count'] for x in win_counts}
    company_ids = set(bid_map.keys()) | set(win_map.keys())
    result = []
    for cid in company_ids:
        bid_count = bid_map.get(cid, 0)
        win_count = win_map.get(cid, 0)
        rate = float(win_count) / float(bid_count) if bid_count > 0 else 0.0
        c = Company.objects.filter(id=cid).values('id', 'company_name').first()
        result.append({'company_id': cid, 'company_name': c['company_name'] if c else '', 'bid_count': bid_count, 'win_count': win_count, 'win_rate': rate})
    result.sort(key=lambda x: x['win_rate'], reverse=True)
    return result


def company_win_rate_precomputed(begin: datetime | None, end: datetime | None, region: str | None, city: str | None, industry: str | None):
    qs = CompanyWinStats.objects.all()
    if begin is not None:
        qs = qs.filter(period__gte=begin.strftime('%Y-%m'))
    if end is not None:
        qs = qs.filter(period__lte=end.strftime('%Y-%m'))
    if region:
        qs = qs.filter(region=region)
    if city:
        qs = qs.filter(city=city)
    if industry:
        qs = qs.filter(industry=industry)
    rows = list(qs.values('company_id').annotate(bid_count=Sum('bid_count'), win_count=Sum('win_count'), win_rate=Sum('win_rate')))
    # 注意：win_rate直接求和并不严格，真实场景应按公司合并比例计算；此处为示例
    result = []
    for r in rows:
        c = Company.objects.filter(id=r['company_id']).values('id', 'company_name').first()
        result.append({'company_id': r['company_id'], 'company_name': c['company_name'] if c else '', 'bid_count': r['bid_count'], 'win_count': r['win_count'], 'win_rate': float(r['win_rate'] or 0)})
    result.sort(key=lambda x: x['win_rate'], reverse=True)
    return result


def industry_hhi(begin: datetime | None, end: datetime | None, region: str | None = None, city: str | None = None, industry: str | None = None):
    wra = WinningResultAnnouncement.objects.select_related('project', 'winning_company')
    if begin is not None:
        wra = wra.filter(announcement_date__gte=begin)
    if end is not None:
        wra = wra.filter(announcement_date__lte=end)
    rows = list(wra.values('project__project_type', 'project__extra_data', 'winning_company_id', 'winning_amount', 'project_id'))
    filtered = []
    for r in rows:
        rg = _get_region(r['project__extra_data'])
        ct = _get_city(r['project__extra_data'])
        ind = _get_industry(r['project__project_type'], r['project__extra_data'])
        if region and rg != region:
            continue
        if city and ct != city:
            continue
        if industry and ind != industry:
            continue
        filtered.append({'industry': ind, 'winning_company_id': r['winning_company_id'], 'amount': float(r['winning_amount'] or 0)})
    groups = {}
    for r in filtered:
        key = r['industry'] or ''
        groups.setdefault(key, {})
        cid = r['winning_company_id']
        amt = r['amount']
        groups[key][cid] = groups[key].get(cid, 0.0) + amt
    result = []
    for k, m in groups.items():
        total = sum(m.values())
        if total <= 0:
            result.append({'industry': k, 'hhi': 0.0, 'top_share': 0.0})
            continue
        shares = [v / total for v in m.values()]
        hhi = sum([(s * 100) ** 2 for s in shares])
        top_share = max(shares) if shares else 0.0
        result.append({'industry': k, 'hhi': hhi, 'top_share': top_share})
    result.sort(key=lambda x: x['hhi'], reverse=True)
    return result


def industry_hhi_precomputed(begin: datetime | None, end: datetime | None, region: str | None, city: str | None, industry: str | None):
    qs = IndustryConcentration.objects.all()
    if begin is not None:
        qs = qs.filter(period__gte=begin.strftime('%Y-%m'))
    if end is not None:
        qs = qs.filter(period__lte=end.strftime('%Y-%m'))
    if region:
        qs = qs.filter(region=region)
    if city:
        qs = qs.filter(city=city)
    if industry:
        qs = qs.filter(industry=industry)
    rows = list(qs.values('industry').annotate(hhi=Sum('hhi'), top_share=Sum('top_share')))
    return [{'industry': r['industry'], 'hhi': float(r['hhi'] or 0), 'top_share': float(r['top_share'] or 0)} for r in rows]
